package com.alan.camunda.boot.jdbc;

import lombok.SneakyThrows;
import org.junit.jupiter.api.Test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

/**
 * 执行 Oracle 存储过程
 */
public class OracleProcedure {

    /**
     * 指定参数名称进行调用
     */
    @SneakyThrows
    @Test
    public void specificName() {
        String url = "jdbc:oracle:thin:@10.2.7.155:1521:bpmdb";
        String user = "lyfbpm";
        String password = "LYF77777";
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);

        String sql = "{call increase_product_price(?)}";
        CallableStatement callStmt = conn.prepareCall(sql);
        callStmt.setInt("p_product_id", 1);

        boolean result = callStmt.execute();
        System.out.println(result);

        conn.close();
    }

    /**
     * 指定参数下标进行调用
     */
    @SneakyThrows
    @Test
    public void specificNameIndex() {
        String url = "jdbc:oracle:thin:@10.2.7.155:1521:bpmdb";
        String user = "lyfbpm";
        String password = "LYF77777";
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);

        String sql = "{call increase_product_price(?)}";
        CallableStatement callStmt = conn.prepareCall(sql);
        callStmt.setInt(1, 1);

        boolean result = callStmt.execute();
        System.out.println(result);

        conn.close();
    }

    @SneakyThrows
    @Test
    public void withReturnValue() {
        String url = "jdbc:oracle:thin:@10.2.7.155:1521:bpmdb";
        String user = "lyfbpm";
        String password = "LYF77777";
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);

        String sql = "{call get_products(?,?)}";
        CallableStatement callStmt = conn.prepareCall(sql);
        int priority = 1;
        callStmt.setInt(1, priority);
        callStmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
        System.out.println("Executing stored procedure...");
        boolean result = callStmt.execute();

        ResultSet rs = (ResultSet) callStmt.getObject(2);

        while (rs.next()) {
            int id = rs.getInt("ID");
            String name = rs.getString("NAME"); // 假设表中有 NAME 字段
            double price = rs.getDouble("PRICE");

            System.out.println("Product ID: " + id + " Name: " + name + " Price: " + price);
        }
        rs.close();
        callStmt.close();
        conn.close();
    }


}
